/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import database.DBConnection;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.PropertiesManager;
import model.Profissional;

/**
 *
 * @author rosana
 */
public class AgendaDAO implements OperacoesDAO {

    private Connection conexao;

    public AgendaDAO() throws ClassNotFoundException, SQLException {
        conexao = DBConnection.getConnection();

    }
    @Override
    public void inserir(Object obj) throws SQLException {
        String SQL = buscaSQL("insert.Agenda");
        if (obj instanceof Agenda) {
            Agenda agenda = (Agenda) obj;
            PreparedStatement st = conexao.prepareStatement(SQL);
            st.setInt(1, agenda.getProfissional().getIdPessoa());
            st.execute();
        }
    }
    
    @Override
    public boolean excluir(Object obj) throws SQLException {
        Integer num = (Integer) obj;
        int id = num.intValue();
        String sql = (String) buscaSQL("delete.Agenda");
        PreparedStatement stmt = conexao.prepareStatement(sql);
        stmt.setInt(1, id);
        stmt.execute();
        stmt.close();
        return true;
    }

    @Override
    public void editar(Object obj) throws SQLException {
        String SQL = buscaSQL("Update.Agenda");
        if (obj instanceof Agenda) {
            Agenda agenda = (Agenda) obj;
            PreparedStatement st = conexao.prepareStatement(SQL);
            st.setInt(1, agenda.getProfissional().getPessoa().getIdPessoa());
            st.setInt(2, agenda.getIdAgenda());
            st.execute();
        }
    }
   
    
    
    

    @Override
    public ArrayList pesquisar() throws SQLException {
        ArrayList retorno = new ArrayList();
        String SQL = buscaSQL("selectAllAgenda");
        Statement st = conexao.createStatement();
        ResultSet res = st.executeQuery(SQL);
        while (res.next()) {
            int key = res.getInt(1);
            String nome = res.getString(2);
            Profissional prof = new Profissional(nome, null, null, null, null, null, null, null);
            Agenda agen = new Agenda(key, prof, null, null, null, null, null, null, null, null);
            retorno.add(agen);
        }
        return retorno;
    }
    
       public ArrayList pesquisarAgenda() throws SQLException {
        ArrayList retorno = new ArrayList();
        String SQL = buscaSQL("selectPesquisaAgenda");
        Statement st = conexao.createStatement();
        ResultSet res = st.executeQuery(SQL);
        while (res.next()) {
            int key = res.getInt(1);
            String nome = res.getString(2);
            int idprof = res.getInt(3);
            Pessoa pes = new Pessoa(idprof, nome, null, null, null, null, null, null, null);
            Profissional prof = new Profissional(pes, 0, null, 0, null, null, null, null, null, null, null, null, null, null, null);
            Agenda agen = new Agenda(key, prof, null, null, null, null, null, null, null, null);
            retorno.add(agen);
        }
        return retorno;
    }
       
       public ArrayList pesquisarId() throws SQLException {
        ArrayList retorno = new ArrayList();
        String SQL = buscaSQL("SelectById.Agenda");
        Statement st = conexao.createStatement();
        ResultSet res = st.executeQuery(SQL);
        while (res.next()) {
            int key = res.getInt(1);
            int idprof = res.getInt(2);
            Pessoa pes = new Pessoa(idprof, null, null, null, null, null, null, null, null);
            Profissional prof = new Profissional(pes, 0, null, 0, null, null, null, null, null, null, null, null, null, null, null);
            Agenda agen = new Agenda(key, prof, null, null, null, null, null, null, null, null);
            retorno.add(agen);
        }
        return retorno;
    }
       
       public Agenda pesquisarId(String chave) throws SQLException {
        Agenda agen = null;
        String SQL = buscaSQL("SelectById.Agenda");
        PreparedStatement st = conexao.prepareStatement(SQL);
        st.setString(1, chave);
        ResultSet res = st.executeQuery();
        while (res.next()) {
           int key = res.getInt(1);
            int idprof = res.getInt(2);
            Pessoa pes = new Pessoa(idprof, null, null, null, null, null, null, null, null);
            Profissional prof = new Profissional(pes, 0, null, 0, null, null, null, null, null, null, null, null, null, null, null);
           agen = new Agenda(key, prof, null, null, null, null, null, null, null, null);
        }
        return agen;
    }
    
     private String buscaSQL(String chave) {
        String sql = null;
        try {
            PropertiesManager pm = new PropertiesManager("sql.properties");
            HashMap dados = pm.readPropertiesFile();
            sql = (String) dados.get(chave);
        } catch (IOException ex) {
            Logger.getLogger(AgendaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sql;
    }    
}
